package com.ruoyi.gd.qybjk.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONObject;
import com.ruoyi.common.core.domain.model.LoginUser;
import com.ruoyi.common.helper.LoginHelper;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.core.domain.PageQuery;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ruoyi.gd.fj.domain.vo.ZFujianVo;
import com.ruoyi.gd.fj.service.IZFujianService;
import com.ruoyi.gd.qybjklog.domain.QybjkLog;
import com.ruoyi.gd.qybjklog.domain.bo.QybjkLogBo;
import com.ruoyi.gd.qybjklog.service.IQybjkLogService;
import com.ruoyi.gd.ywd.domain.ZYwd;
import lombok.RequiredArgsConstructor;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import com.ruoyi.gd.qybjk.domain.bo.ZQybjkBo;
import com.ruoyi.gd.qybjk.domain.vo.ZQybjkVo;
import com.ruoyi.gd.qybjk.domain.ZQybjk;
import com.ruoyi.gd.qybjk.mapper.ZQybjkMapper;
import com.ruoyi.gd.qybjk.service.IZQybjkService;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;

/**
 * 企业报价库Service业务层处理
 *
 * @author ruoyi
 * @date 2024-07-19
 */
@RequiredArgsConstructor
@Service
public class ZQybjkServiceImpl implements IZQybjkService {

    private final ZQybjkMapper baseMapper;

    @Autowired
    private IZFujianService fujianService;

    @Autowired
    private IQybjkLogService qybjdLogService;

    @Value("${upload.path}")
    private String uploadPath;

    /**
     * 查询企业报价库
     */
    @Override
    public ZQybjkVo queryById(String id){
        return baseMapper.selectVoById(id);
    }

    /**
     * 查询企业报价库列表
     */
    @Override
    public TableDataInfo<ZQybjkVo> queryPageList(ZQybjkBo bo, PageQuery pageQuery) {
        LambdaQueryWrapper<ZQybjk> lqw = buildQueryWrapper(bo);
        Page<ZQybjkVo> result = baseMapper.selectVoPage(pageQuery.build(), lqw);
        return TableDataInfo.build(result);
    }

    /**
     * 查询企业报价库列表
     */
    @Override
    public List<ZQybjkVo> queryList(ZQybjkBo bo) {
        LambdaQueryWrapper<ZQybjk> lqw = buildQueryWrapper(bo);
        return baseMapper.selectVoList(lqw);
    }

    private LambdaQueryWrapper<ZQybjk> buildQueryWrapper(ZQybjkBo bo) {
        LambdaQueryWrapper<ZQybjk> lqw = Wrappers.lambdaQuery();
        try {
            lqw.like(StringUtils.isNotBlank(bo.getXh()), ZQybjk::getXh, bo.getXh());
            lqw.like(StringUtils.isNotBlank(bo.getYqmc()), ZQybjk::getYqmc, bo.getYqmc());
            lqw.like(StringUtils.isNotBlank(bo.getXhgg()), ZQybjk::getXhgg, bo.getXhgg());
            lqw.like(StringUtils.isNotBlank(bo.getZzc()), ZQybjk::getZzc, bo.getZzc());
            lqw.like(StringUtils.isNotBlank(bo.getCcbh()), ZQybjk::getCcbh, bo.getCcbh());
            lqw.like(StringUtils.isNotBlank(bo.getGlbh()), ZQybjk::getGlbh, bo.getGlbh());
            lqw.eq(bo.getSl() != null, ZQybjk::getSl, bo.getSl());
            lqw.eq(bo.getDj() != null, ZQybjk::getDj, bo.getDj());
            lqw.eq(bo.getXj() != null, ZQybjk::getXj, bo.getXj());
            lqw.like(StringUtils.isNotBlank(bo.getFwfs()), ZQybjk::getFwfs, bo.getFwfs());
            lqw.like(StringUtils.isNotBlank(bo.getBz()), ZQybjk::getBz, bo.getBz());
            lqw.like(StringUtils.isNotBlank(bo.getWgbh()), ZQybjk::getWgbh, bo.getWgbh());
            lqw.like(StringUtils.isNotBlank(bo.getKhname()), ZQybjk::getKhname, bo.getKhname());
            if (bo.getParams()!=null && bo.getParams().size() > 0) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                Object start = bo.getParams().get("startTime");
                Object end = bo.getParams().get("endTime");
                if (null != start && null != end) {
                    Date startTime = format.parse((String) start);
                    Date endTime = format.parse((String) end);
                    if (startTime != null && endTime != null) {
                        lqw.ge(ZQybjk::getWgsj, startTime);
                        lqw.le(ZQybjk::getWgsj, endTime);
                    }
                }
            }
            // 从前端获取排序
            String orderByCode = bo.getOrderByCode();
            if (StringUtils.isNotEmpty(orderByCode)) {
                lqw = getOrder(lqw, orderByCode);
            }else {
                lqw.orderByDesc(ZQybjk::getWgsj);
                lqw.orderByAsc(ZQybjk::getXh);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return lqw;
    }

    private LambdaQueryWrapper<ZQybjk> getOrder(LambdaQueryWrapper<ZQybjk> lqw, String orderByCode) {
        String[] split = orderByCode.split(",");
        String file = split[0];
        String order = split[1];
        switch (file) {
            case "wgbh":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getWgbh) : lqw.orderByDesc(ZQybjk::getWgbh);
                break;
            case "yqmc":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getYqmc) : lqw.orderByDesc(ZQybjk::getYqmc);
                break;
            case "xhgg":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getXhgg) : lqw.orderByDesc(ZQybjk::getXhgg);
                break;
            case "zzc":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getZzc) : lqw.orderByDesc(ZQybjk::getZzc);
                break;
            case "ccbh":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getCcbh) : lqw.orderByDesc(ZQybjk::getCcbh);
                break;
            case "glbh":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getGlbh) : lqw.orderByDesc(ZQybjk::getGlbh);
                break;
            case "sl":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getSl) : lqw.orderByDesc(ZQybjk::getSl);
                break;
            case "dj":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getDj) : lqw.orderByDesc(ZQybjk::getDj);
                break;
            case "xj":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getXj) : lqw.orderByDesc(ZQybjk::getXj);
                break;
            case "fwfs":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getFwfs) : lqw.orderByDesc(ZQybjk::getFwfs);
                break;
            case "bz":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getBz) : lqw.orderByDesc(ZQybjk::getBz);
                break;
            case "khname":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getKhname) : lqw.orderByDesc(ZQybjk::getKhname);
                break;
            case "wgsj":
                lqw = "asc".equals(order)?lqw.orderByAsc(ZQybjk::getWgsj) : lqw.orderByDesc(ZQybjk::getWgsj);
                break;
        }
        return lqw;
    }


    /**
     * 新增企业报价库
     */
    @Override
    public Boolean insertByBo(ZQybjkBo bo) {
        ZQybjk add = BeanUtil.toBean(bo, ZQybjk.class);
        validEntityBeforeSave(add);
        boolean flag = baseMapper.insert(add) > 0;
        if (flag) {
            bo.setId(add.getId());
        }
        return flag;
    }

    /**
     * 修改企业报价库
     */
    @Override
    public Boolean updateByBo(ZQybjkBo bo) {
        ZQybjk update = BeanUtil.toBean(bo, ZQybjk.class);
        validEntityBeforeSave(update);
        return baseMapper.updateById(update) > 0;
    }

    /**
     * 保存前的数据校验
     */
    private void validEntityBeforeSave(ZQybjk entity){
        //TODO 做一些数据校验,如唯一约束
    }

    /**
     * 批量删除企业报价库
     */
    @Override
    public Boolean deleteWithValidByIds(Collection<String> ids, Boolean isValid) {
        if(isValid){
            //TODO 做一些业务上的校验,判断是否需要校验
        }
        return baseMapper.deleteBatchIds(ids) > 0;
    }

    /**
     * 导入企业报价库数据
     * @param fjId
     * @return
     */
    @Override
    public int importQybjdData(String fjId) {
        List<ZFujianVo> zFujianVos = fujianService.queryListByIdOrdery(fjId);
        int row = 0;
        for (ZFujianVo zFujianVo : zFujianVos) {
            row += importExcelData(zFujianVo);
        }
        return row;
    }

    /**
     * 导入excel数据
     * 1. 读取excel数据
     * 2. 获取excel中的完工编号，通过完工编号查询数据，将所有的数删除
     * 3. 获取excel的客户名称，完工时间，如果没有完工时间，则获取当前的文件的最后修改时间
     */
    private int importExcelData(ZFujianVo zFujianVo) {
        String fjpath = uploadPath + zFujianVo.getFjpath();
        LoginUser loginUser = LoginHelper.getLoginUser();
        FileInputStream fis = null;
        String wgbh = "";
        File file = new File(fjpath);
        try  {
            fis = new FileInputStream(file);
            ZipSecureFile.setMinInflateRatio(0.0);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            // 获取当前的完工编号
            wgbh = getWgbh(sheet, lastRowNum);
            // 通过当前的完工编号删除历史数据
            baseMapper.deleteByWgbh(wgbh);
            // 删除后，获取excel的客户名称和日期，判断当前列表的表头在第一行，以及一个表头在第几列
            JSONObject jsonObject = getDatas(sheet, lastRowNum);
            List<ZQybjk> zQybjkList = new ArrayList<>();
            int pxNum = 1;
            int djNum = 0;
            // 获取数据
            for (int i = 0; i < lastRowNum; i++) {
                // 判断当前的行单价数量连续为0的话，说明是完工单，则跳过数据
                if(djNum >=2 ) {
                    break;
                }
                // 获取当前行
                Row row = sheet.getRow(i);
                // 获取编号是在第几行
                Integer xhRow = jsonObject.getInt("xh_row");
                // 从序号的下一行开始获取数据
                if (i > xhRow) {
                    Cell xh = row.getCell(jsonObject.getInt("xh_column"));
                    Cell yqmc = row.getCell(jsonObject.getInt("yqmc_column"));
                    Cell xhgg = row.getCell(jsonObject.getInt("xhgg_column"));
                    Cell sl = row.getCell(jsonObject.getInt("sl_column"));
                    Cell dj = row.getCell(jsonObject.getInt("dj_column"));
                    if (xh != null && StringUtils.isNotEmpty(xh.toString())
                        && (xh.toString().contains("校准总费用") || xh.toString().contains("现场服务费"))
                    ) {
                        break;
                    } else if (xh != null && StringUtils.isNotEmpty(xh.toString())
                        && yqmc != null && StringUtils.isNotEmpty(yqmc.toString())
                        && xhgg != null && StringUtils.isNotEmpty(xhgg.toString())
                        && sl != null && StringUtils.isNotEmpty(sl.toString())
                        && dj != null && StringUtils.isNotEmpty(dj.toString())
                    ) {
                        Cell zzc = row.getCell(jsonObject.getInt("zzc_column"));
                        Cell ccbh = row.getCell(jsonObject.getInt("ccbh_column"));
                        Cell glbh = row.getCell(jsonObject.getInt("glbh_column"));
                        Cell fwfs = row.getCell(jsonObject.getInt("fwfs_column"));
                        Cell bz = row.getCell(jsonObject.getInt("bz_column"));
                        ZQybjk zQybjk = new ZQybjk();
                        zQybjk.setId(IdUtil.simpleUUID());
                        zQybjk.setXh(pxNum+"");
                        zQybjk.setYqmc(yqmc.toString());
                        zQybjk.setXhgg(xhgg.toString());
                        zQybjk.setZzc(zzc.toString());
                        zQybjk.setCcbh(ccbh.toString());
                        zQybjk.setGlbh(glbh.toString());
                        zQybjk.setSl(sl != null ? Double.parseDouble(sl.toString()) : 0);
                        if (dj != null) {
                            double v = Double.parseDouble(dj.toString());
                            if (v == 0 || v == 0.0) {
                                djNum++;
                            }
                            zQybjk.setDj(v);
                        }else {
                            zQybjk.setDj(0.0);
                        }
                        zQybjk.setXj(zQybjk.getSl() * zQybjk.getDj());
                        zQybjk.setFwfs(fwfs.toString());
                        zQybjk.setBz(bz.toString());
                        zQybjk.setWgbh(wgbh);
                        zQybjk.setKhname(jsonObject.getStr("khName"));
                        // 设置完工时间
                        Date wgrq = jsonObject.getDate("wgrq");
                        if (wgrq != null) {
                            zQybjk.setWgsj(wgrq);
                        }else {
                            // 使用 lastModified() 方法获取文件的最后修改时间（毫秒数）
                            long lastModified = file.lastModified();
                            zQybjk.setWgsj(new Date(lastModified));
                        }
                        zQybjkList.add(zQybjk);
                        pxNum++;
                    }
                }
            }
            QybjkLogBo qybjkLog = new QybjkLogBo();
            qybjkLog.setYwid(wgbh); // 设置完工编号
            qybjkLog.setCreateTime(new Date());
            qybjkLog.setCreateBy(loginUser.getNickName());
            qybjkLog.setFilename(zFujianVo.getFjname());
            if (djNum >= 2) {
                qybjkLog.setMsg("上传的文件名称为："+zFujianVo.getFjname()+"。完工编号为： "+wgbh+" 的Excel导入失败。原因：当前的数据单价连续两行为0；系统判定当前的文件是未报价的完工单");
                qybjkLog.setStatus("20");
            }else {
                // 如果没有问题在添加
                baseMapper.insertBatch(zQybjkList);
                qybjkLog.setMsg("上传的文件名称为："+zFujianVo.getFjname()+"。完工编号为： "+wgbh+" 的Excel导入成功。导入了"+zQybjkList.size()+"条数据。");
                qybjkLog.setStatus("10");
            }
            qybjdLogService.insertByBo(qybjkLog);
            return 1;
        } catch (Exception e) {
            QybjkLogBo qybjkLog = new QybjkLogBo();
            qybjkLog.setYwid(wgbh); // 设置完工编号
            qybjkLog.setMsg("上传的文件名称为："+zFujianVo.getFjname()+"。完工编号为： "+wgbh+" 的Excel导入失败。失败原因："+e.getMessage());
            qybjkLog.setCreateTime(new Date());
            qybjkLog.setCreateBy(loginUser.getNickName());
            qybjkLog.setStatus("20");
            qybjkLog.setFilename(zFujianVo.getFjname());
            qybjdLogService.insertByBo(qybjkLog);
            e.printStackTrace();
            return 0;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    // 获取当前的合同编号
    private String getWgbh(Sheet sheet, int lastRowNum) {
        String wgbb = "";
        a:for (int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    String cellValue = cell.toString();
                    if (cellValue.contains("合同单号")) {
                        String[] split = cellValue.split("[:：]");
                        if (split.length>0) {
//                            wgbb = split[1].trim().replaceAll("\"", "");
                            wgbb = split[1].trim();
                        }
                        break a;
                    }
                }
            }
        }
        return wgbb;
    }

    // 获取excel的客户名称和日期，判断当前列表的表头在第一行，以及一个表头在第几列
    private JSONObject getDatas(Sheet sheet, int lastRowNum) {
        JSONObject jsonObject = new JSONObject();
        boolean xh = false; boolean yqmc = false; boolean xhgg = false; boolean zzc = false;
        boolean ccbh = false; boolean glbh = false; boolean sl = false; boolean dj = false; boolean xj = false;
        boolean fwfs = false; boolean bz = false;
        a:for (int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        String cellValue = cell.toString().trim();
                        if (cellValue.contains("委托单位（甲方）")) { // 处理客户名称
                            String[] split = cellValue.split("[:：]");
                            if (split.length>0) {
                                split[1].trim();
                                jsonObject.put("khName", split[1].trim());
                            }else {
                                jsonObject.put("khName", "");
                            }
                        }else if ("序号".equals(cellValue)  && !xh) { // 判断当前的序号位置
                            jsonObject.put("xh_row", i);
                            jsonObject.put("xh_column",j);
                            xh = true;
                        }else if ("仪器名称".equals(cellValue)  && !yqmc) { // 判断当前的仪器名称位置
                            jsonObject.put("yqmc_row", i);
                            jsonObject.put("yqmc_column", j);
                            yqmc = true;
                        }else if ("型号/规格".equals(cellValue)  && !xhgg) { // 判断当前的型号/规格位置
                            jsonObject.put("xhgg_row", i);
                            jsonObject.put("xhgg_column", j);
                            xhgg = true;
                        }else if ("制造厂".equals(cellValue)  && !zzc) { // 判断当前的制造厂位置
                            jsonObject.put("zzc_row", i);
                            jsonObject.put("zzc_column", j);
                            zzc = true;
                        }else if ("出厂编号".equals(cellValue)  && !ccbh) { // 判断当前的出厂编号位置
                            jsonObject.put("ccbh_row", i);
                            jsonObject.put("ccbh_column", j);
                            ccbh = true;
                        }else if ("管理编号".equals(cellValue)  && !glbh) { // 判断当前的管理编号位置
                            jsonObject.put("glbh_row", i);
                            jsonObject.put("glbh_column", j);
                            glbh = true;
                        }else if ("数量".equals(cellValue)  && !sl) { // 判断当前的数量位置
                            jsonObject.put("sl_row", i);
                            jsonObject.put("sl_column", j);
                            sl = true;
                        }else if ("单价（元）".equals(cellValue)  && !dj) { // 判断当前的单价（元）位置
                            jsonObject.put("dj_row", i);
                            jsonObject.put("dj_column", j);
                            dj = true;
                        }else if ("小计（元)".equals(cellValue)  && !xj) { // 判断当前的小计（元)位置
                            jsonObject.put("xj_row", i);
                            jsonObject.put("xj_column", j);
                            xj = true;
                        }else if ("服务方式".equals(cellValue)  && !fwfs) { // 判断当前的服务方式位置
                            jsonObject.put("fwfs_row", i);
                            jsonObject.put("fwfs_column", j);
                            fwfs = true;
                        }else if ("备注".equals(cellValue)  && !bz) { // 判断当前的备注位置
                            jsonObject.put("bz_row", i);
                            jsonObject.put("bz_column", j);
                            bz = true;
                        }else {
                            boolean validDate = isValidDate(cellValue);
                            if (validDate) {
                                jsonObject.put("wgrq", cellValue);
                            }else {
                                if (cellValue.contains("日期")) { // 处理完工日期
                                    String[] split = cellValue.split("[:：]");
                                    if (split.length>0) {
                                        // 解析当前的日期
                                        LocalDate localDate = parseDate(split[1]);
                                        jsonObject.put("wgrq", localDate);
                                    }
                                }
                            }
                        }

                    }
                }
            }

        }
        return jsonObject;
    }

    // 解析日期
    private LocalDate parseDate(String dateStr) {
        // 定义可能的日期格式列表
        List<DateTimeFormatter> formatters = Arrays.asList(
            DateTimeFormatter.ofPattern("yyyy-MM-dd"),
            DateTimeFormatter.ofPattern("yyyy.MM.dd"),
            DateTimeFormatter.ofPattern("yyyy.M.d"),
            DateTimeFormatter.ofPattern("yyyy年MM月dd日")
        );
        // 尝试解析日期
        LocalDate date = null;
        for (DateTimeFormatter formatter : formatters) {
            try {
                date = LocalDate.parse(dateStr, formatter);
                break; // 成功解析后退出循环
            } catch (DateTimeParseException e) {}
        }
        return date;
    }

    public boolean isValidDate(String dateString) {
        String[] datePatterns = {
            "yyyy-MM-dd",   // ISO 8601
            "yyyy.MM.dd",   // Dots as separators
            "yyyy.M.d",     // Single dot and single month day
            "yyyy年MM月dd日" // Chinese style
        };
        for (String pattern : datePatterns) {
            try {
                DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
                LocalDate.parse(dateString, formatter);
                return true; // If parsing succeeds, the format is valid.
            } catch (DateTimeParseException e) {
                // Ignored, try next pattern.
            }
        }
        return false; // No pattern matched.
    }

}
